package ru.averta.notes.dao.impl;

import org.hibernate.SQLQuery;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.hibernate.type.LongType;
import org.hibernate.type.StringType;
import org.springframework.stereotype.Repository;
import ru.averta.notes.dao.UrlDao;
import ru.averta.notes.domain.Url;
import ru.averta.notes.domain.User;

import java.util.Collection;
import java.util.LinkedList;
import java.util.List;

/**
 * <code>SiteContentDaoImpl</code>
 *
 * @author fedor.belov (fedor.belov@mail.ru)
 *         Date: 19.09.11 14:41
 */

@Repository
public class UrlDaoImpl extends GenericDaoHibernateImpl<Url, Long> implements UrlDao {

    @Override
    public Url findUrlObjectByUrl(String url) {
        return (Url) createCriteria().add(Restrictions.eq("url", url)).uniqueResult();
    }

    @Override
    public Collection<Long> findUrlsByUser(User user) {
        List ids = createCriteria().createAlias("sites", "s").add(Restrictions.eq("s.user", user)).setProjection(Projections.property("id")).list();

        Collection<Long> answer = new LinkedList<Long>();
        for (Object id : ids) {
            answer.add((Long) id);
        }

        return answer;
    }

    @Override
    public List<Object[]> findUrlsAndTagsByUser(User user) {
        SQLQuery query = createSqlQuery("SELECT URL.ID, TAG.Title FROM SITE, TAG_CONNECTOR, TAG, URL " +
                "WHERE (SITE.USER_ID = :userId) and (TAG_CONNECTOR.SUBJECT_ID = SITE.ID) " +
                "and (TAG.ID = TAG_CONNECTOR.TAG_ID) " +
                "and (URL.ID = SITE.URL_ID) " +
                "ORDER BY URL.ID");

        query.setLong("userId", user.getId());
        query.addScalar("URL.ID", LongType.INSTANCE);
        query.addScalar("TAG.Title", StringType.INSTANCE);

        return query.list();
    }

    @Override
    public List<Object[]> findTagsAndUrlsByUser(User user) {
        SQLQuery query = createSqlQuery("SELECT TAG.Title, URL.ID FROM SITE, TAG_CONNECTOR, TAG, URL " +
                "WHERE (SITE.USER_ID = :userId) and (TAG_CONNECTOR.SUBJECT_ID = SITE.ID) " +
                "and (TAG.ID = TAG_CONNECTOR.TAG_ID) " +
                "and (URL.ID = SITE.URL_ID) " +
                "ORDER BY TAG.Title");

        query.setLong("userId", user.getId());
        query.addScalar("TAG.Title", StringType.INSTANCE);
        query.addScalar("URL.ID", LongType.INSTANCE);

        return query.list();
    }
}
